package com.gugu.procedure.util;

import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.gugu.procedure.bean.InsideOrder;
import com.gugu.procedure.bean.OrderItem;
import com.gugu.procedure.bean.Parameter;
import com.gugu.procedure.bean.WorkDone;
import com.gugu.procedure.common.Constant;
import com.gugu.procedure.exceptions.BusinessException;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OrderUtil {

    public static void main(String[] args) throws Exception {
        InsideOrder insideOrder = new InsideOrder();
        insideOrder.setStatus("1");
        insideOrder.setBusinessDate("2011-12-10");
        insideOrder.setBusiness("业务小张");
        insideOrder.setQcDate("品管员");
        insideOrder.setQcDate("2013-10-19");
        insideOrder.setTechnologyDate("技术小刘");
        insideOrder.setTechnologyDate("2018-10-01");
        insideOrder.setPlanDate("计划员");
        insideOrder.setPlanDate("2019-10-01");
        insideOrder.setReason("这是一个原因字段");
        insideOrder.setReplyDate("2091-09-01");
        insideOrder.setCustOrderNo("000001");
        insideOrder.setOrderNo("000001");
        insideOrder.setCustCompany("通亚");
        insideOrder.setCustomer("上帝客户");
        insideOrder.setMaterial("镀银锡铜合金线");
        insideOrder.setSpecialRequirements("你需要特别小心听到没有");
        insideOrder.setRequirement("用心做");
        insideOrder.setRemark("请注意请注意");
        insideOrder.setProduction("生产负责人");
        insideOrder.setProductionDate("2091-09-01");

        OrderItem o1 = new OrderItem();
        o1.setNumberEnd("1111");
        o1.setNumberStart("222");
        o1.setRoughItemNo("222");
        o1.setRoughSupplier("232");
        o1.setType("1");
        o1.setOrderNo("00001");
        o1.setReverse9("90");
        o1.setFront3("02");
        o1.setFront9("022");
        o1.setToleranceUp("1111");
        o1.setReverse3("111");
        o1.setSeque("1");
        o1.setProductName("产品");
        o1.setCustProcuctName("1111");
        o1.setExport("222");
        o1.setAmount("1");
        o1.setExcessive("111");
        o1.setUnit("dd");
        OrderItem o2 = new OrderItem();
        BeanUtil.copyProperties(o1, o2);
        List<OrderItem> list = new ArrayList<>();
        list.add(o1);
        list.add(o2);

        List<Parameter> parameters = new ArrayList<>();
        Parameter p = new Parameter();
        p.setTangentValue("11");
        Parameter p2 = new Parameter();
        p2.setTangentValue("11");
        parameters.add(p);
        parameters.add(p2);
        // exportOrder(insideOrder, list, parameters)


        File file = new File("E:\\test\\1.xls");
        resolvingInsideOrder(file, "N");

    }

    /**
     * 订单导出
     *
     * @param insideOrder 订单
     * @param orderItems  订单项
     * @param parameters  订单参数
     */
    public static void exportOrder(HttpServletResponse response, InsideOrder insideOrder, List<OrderItem> orderItems, List<Parameter> parameters) {
        HSSFWorkbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("订单信息");
        // 构造第一行 订单号
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont f = wb.createFont();
        f.setFontHeightInPoints((short) 18);// 字号
        f.setBold(true);//加粗
        style.setFont(f);
        style.setAlignment(HorizontalAlignment.CENTER); //左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); //上下居中
        Row row = sheet.createRow(0);
        row.setHeight((short) 600);// 设置高度
        CellRangeAddress callRangeAddress1 = new CellRangeAddress(0, 0, 0, 20);
        sheet.addMergedRegion(callRangeAddress1);
        Cell cell = row.createCell(0, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("内部订单号");

        //处理客户公司名称
        CellRangeAddress callRangeAddress2 = new CellRangeAddress(1, 1, 0, 20);
        sheet.addMergedRegion(callRangeAddress2);
        Row row2 = sheet.createRow(1);
        row2.createCell(0, CellType.STRING).setCellValue(insideOrder.getCustCompany());
        //处理客户订单号
        CellRangeAddress callRangeAddress3 = new CellRangeAddress(2, 2, 0, 20);
        sheet.addMergedRegion(callRangeAddress3);
        Row row3 = sheet.createRow(2);
        row3.createCell(0, CellType.STRING).setCellValue("客户订单号：" + StringUtil.nullHandle(insideOrder.getCustOrderNo()));

        //处理客户  伸线材质  内部订单号
        CellRangeAddress callRangeAddress4 = new CellRangeAddress(3, 3, 0, 3);
        sheet.addMergedRegion(callRangeAddress4);

        CellRangeAddress callRangeAddress5 = new CellRangeAddress(3, 3, 4, 7);
        sheet.addMergedRegion(callRangeAddress5);

        CellRangeAddress callRangeAddress6 = new CellRangeAddress(3, 3, 8, 11);
        sheet.addMergedRegion(callRangeAddress6);

        Row row4 = sheet.createRow(3);
        row4.createCell(0, CellType.STRING).setCellValue("客户：" + StringUtil.nullHandle(insideOrder.getCustomer()));
        row4.createCell(4, CellType.STRING).setCellValue("伸线材质：" + StringUtil.nullHandle(insideOrder.getMaterial()));
        row4.createCell(8, CellType.STRING).setCellValue("内部订单号：" + StringUtil.nullHandle(insideOrder.getOrderNo()));

        //处理 订单日期  物料齐备日期  要求交期  回复日期
        CellRangeAddress callRangeAddress7 = new CellRangeAddress(4, 4, 0, 3);
        sheet.addMergedRegion(callRangeAddress7);

        CellRangeAddress callRangeAddress8 = new CellRangeAddress(4, 4, 4, 7);
        sheet.addMergedRegion(callRangeAddress8);

        CellRangeAddress callRangeAddress9 = new CellRangeAddress(4, 4, 8, 11);
        sheet.addMergedRegion(callRangeAddress9);
        CellRangeAddress callRangeAddress10 = new CellRangeAddress(4, 4, 13, 17);
        sheet.addMergedRegion(callRangeAddress10);
        Row row5 = sheet.createRow(4);
        row5.createCell(0, CellType.STRING).setCellValue("订单日期：" + StringUtil.nullHandle(insideOrder.getOrderDate()));
        row5.createCell(4, CellType.STRING).setCellValue("物料齐备日期：" + StringUtil.nullHandle(insideOrder.getReadyDate()));
        row5.createCell(8, CellType.STRING).setCellValue("要求交期：" + StringUtil.nullHandle(insideOrder.getRequireDate()));
        row5.createCell(13, CellType.STRING).setCellValue("回复日期：" + StringUtil.nullHandle(insideOrder.getReplyDate()));

        //客户要求
        Row row6 = sheet.createRow(5);
        CellRangeAddress callRangeAddress11 = new CellRangeAddress(5, 5, 0, 20);
        sheet.addMergedRegion(callRangeAddress11);
        row6.createCell(0, CellType.STRING).setCellValue("客户要求：" + StringUtil.nullHandle(insideOrder.getRequirement()));

        // 特殊要求
        if (StringUtil.isNotEmpty(insideOrder.getSpecialRequirements())) {
            Row row7 = sheet.createRow(6);
            CellRangeAddress callRangeAddress12 = new CellRangeAddress(6, 6, 0, 20);
            sheet.addMergedRegion(callRangeAddress12);
            row7.createCell(0, CellType.STRING).setCellValue("特殊要求：" + StringUtil.nullHandle(insideOrder.getSpecialRequirements()));
        }

        // 处理订单项表头
        Row row8 = sheet.createRow(7);
        row8.createCell(0, CellType.STRING).setCellValue("序号");
        row8.createCell(1, CellType.STRING).setCellValue("存货代码");
        row8.createCell(2, CellType.STRING).setCellValue("客户品名");
        row8.createCell(3, CellType.STRING).setCellValue("出口");
        row8.createCell(4, CellType.STRING).setCellValue("类型备注");
        row8.createCell(5, CellType.STRING).setCellValue("规格");
        row8.createCell(6, CellType.STRING).setCellValue("单位");
        row8.createCell(7, CellType.STRING).setCellValue("数量");
        row8.createCell(8, CellType.STRING).setCellValue("公差下限");
        row8.createCell(9, CellType.STRING).setCellValue("公差上限");
        row8.createCell(10, CellType.STRING).setCellValue("外壳尺寸");
        row8.createCell(11, CellType.STRING).setCellValue("客户指定料号");
        row8.createCell(12, CellType.STRING).setCellValue("压缩角/公差下限");
        row8.createCell(13, CellType.STRING).setCellValue("压缩角/公差上限");
        row8.createCell(14, CellType.STRING).setCellValue("定径公差下限");
        row8.createCell(15, CellType.STRING).setCellValue("定径公差上限");
        row8.createCell(16, CellType.STRING).setCellValue("供应商代码");
        row8.createCell(17, CellType.STRING).setCellValue("料号");
        row8.createCell(18, CellType.STRING).setCellValue("压缩角公差下限2");
        row8.createCell(19, CellType.STRING).setCellValue("压缩角公差上限2");
        row8.createCell(20, CellType.STRING).setCellValue("定径公差下限2");
        row8.createCell(21, CellType.STRING).setCellValue("定径公差上限2");
        row8.createCell(22, CellType.STRING).setCellValue("客户代码");
        row8.createCell(23, CellType.STRING).setCellValue("模具起始编号(反面12点方向)");
        row8.createCell(24, CellType.STRING).setCellValue("模具结束编号(反面12点方向)");
        row8.createCell(25, CellType.STRING).setCellValue("正面9点");
        row8.createCell(26, CellType.STRING).setCellValue("正面3点");
        row8.createCell(27, CellType.STRING).setCellValue("反面9点");
        row8.createCell(28, CellType.STRING).setCellValue("反面3点");

        int index = 8;
        int totalAmount = 0;
        for (OrderItem orderItem : orderItems) {
            Row itemRow = sheet.createRow(index++);
            itemRow.createCell(0, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getSeque()));
            itemRow.createCell(1, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getProductName()));
            itemRow.createCell(2, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getCustProcuctName()));
            itemRow.createCell(3, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getExport()));
            itemRow.createCell(4, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getExcessive()));
            itemRow.createCell(5, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getSpecs()));
//            itemRow.createCell(6, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getSpecs()));
            itemRow.createCell(6, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getUnit()));
            itemRow.createCell(7, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getAmount()));
            itemRow.createCell(8, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getToleranceUp()));
            itemRow.createCell(9, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getToleranceDown()));
            itemRow.createCell(10, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getShellSize()));
            itemRow.createCell(11, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getItemNo()));
            itemRow.createCell(12, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getCompressDown()));
            itemRow.createCell(13, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getCompressUp()));
            itemRow.createCell(14, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getDingjingDown()));
            itemRow.createCell(15, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getDingjingUp()));
            itemRow.createCell(16, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getRoughSupplier()));
            itemRow.createCell(17, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getRoughItemNo()));
            itemRow.createCell(18, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getCompressDown2()));
            itemRow.createCell(19, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getCompressUp2()));
            itemRow.createCell(20, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getDingjingDown2()));
            itemRow.createCell(21, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getDingjingUp2()));
            itemRow.createCell(22, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getCustNo()));
            itemRow.createCell(23, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getNumberStart()));
            itemRow.createCell(24, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getNumberEnd()));
            itemRow.createCell(25, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getFront9()));
            itemRow.createCell(26, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getFront3()));
            itemRow.createCell(27, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getReverse9()));
            itemRow.createCell(28, CellType.STRING).setCellValue(StringUtil.nullHandle(orderItem.getReverse3()));
            totalAmount = totalAmount + Integer.parseInt(StringUtil.nullHandle(orderItem.getAmount()));
        }
        //处理合计
        row = sheet.createRow(index++);
        row.createCell(0, CellType.STRING).setCellValue("合计：");
        row.createCell(8, CellType.STRING).setCellValue(totalAmount + "");
        // 合计下面是空行 index++处理
        index++;
        //处理备注
        row = sheet.createRow(index++);
        row.createCell(0, CellType.STRING).setCellValue("注：" + insideOrder.getRemark());

        //处理部门
        CellRangeAddress callRangeAddress13 = new CellRangeAddress(index, index, 0, 3);
        sheet.addMergedRegion(callRangeAddress13);

        CellRangeAddress callRangeAddress14 = new CellRangeAddress(index, index, 4, 7);
        sheet.addMergedRegion(callRangeAddress14);

        CellRangeAddress callRangeAddress15 = new CellRangeAddress(index, index, 8, 11);
        sheet.addMergedRegion(callRangeAddress15);

        CellRangeAddress callRangeAddress16 = new CellRangeAddress(index, index, 12, 15);
        sheet.addMergedRegion(callRangeAddress16);

        CellRangeAddress callRangeAddress17 = new CellRangeAddress(index, index, 16, 19);
        sheet.addMergedRegion(callRangeAddress17);

        row = sheet.createRow(index);
        row.createCell(0, CellType.STRING).setCellValue("生产课：" + StringUtil.nullHandle(insideOrder.getProduction()));
        row.createCell(4, CellType.STRING).setCellValue("品管：" + StringUtil.nullHandle(insideOrder.getQc()));
        row.createCell(8, CellType.STRING).setCellValue("计划：" + StringUtil.nullHandle(insideOrder.getPlan()));
        row.createCell(12, CellType.STRING).setCellValue("技术：" + StringUtil.nullHandle(insideOrder.getTechnology()));
        row.createCell(16, CellType.STRING).setCellValue("业务：" + StringUtil.nullHandle(insideOrder.getBusiness()));


        index++;
        CellRangeAddress callRangeAddress18 = new CellRangeAddress(index, index, 0, 3);
        sheet.addMergedRegion(callRangeAddress18);

        CellRangeAddress callRangeAddress19 = new CellRangeAddress(index, index, 4, 7);
        sheet.addMergedRegion(callRangeAddress19);

        CellRangeAddress callRangeAddress20 = new CellRangeAddress(index, index, 8, 11);
        sheet.addMergedRegion(callRangeAddress20);

        CellRangeAddress callRangeAddress21 = new CellRangeAddress(index, index, 12, 15);
        sheet.addMergedRegion(callRangeAddress21);

        CellRangeAddress callRangeAddress22 = new CellRangeAddress(index, index, 16, 19);
        sheet.addMergedRegion(callRangeAddress22);

        row = sheet.createRow(index);
        row.createCell(0, CellType.STRING).setCellValue("日期：" + StringUtil.nullHandle(insideOrder.getProductionDate()));
        row.createCell(4, CellType.STRING).setCellValue("日期：" + StringUtil.nullHandle(insideOrder.getQcDate()));
        row.createCell(8, CellType.STRING).setCellValue("日期：" + StringUtil.nullHandle(insideOrder.getPlanDate()));
        row.createCell(12, CellType.STRING).setCellValue("日期：" + StringUtil.nullHandle(insideOrder.getTechnologyDate()));
        row.createCell(16, CellType.STRING).setCellValue("日期：" + StringUtil.nullHandle(insideOrder.getBusinessDate()));


        //处理第二个订单参数sheet
        Sheet sheet2 = wb.createSheet("订单参数信息");
        row = sheet2.createRow(0);
        cell = row.createCell(0, CellType.STRING);
        cell.setCellStyle(style);
        row.setHeight((short) 600);// 设置高度
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, 20);
        sheet2.addMergedRegion(range);
        cell.setCellValue("单号" + insideOrder.getOrderNo() + "参数表");


        //序号单元格
        range = new CellRangeAddress(1, 2, 0, 0);
        sheet2.addMergedRegion(range);

        //材质
        range = new CellRangeAddress(1, 2, 1, 1);
        sheet2.addMergedRegion(range);
        //规格
        range = new CellRangeAddress(1, 2, 2, 2);
        sheet2.addMergedRegion(range);

        //需到  孔径
        range = new CellRangeAddress(1, 2, 3, 3);
        sheet2.addMergedRegion(range);
        //角度
        range = new CellRangeAddress(1, 2, 4, 4);
        sheet2.addMergedRegion(range);
        //定径  长度
        range = new CellRangeAddress(1, 2, 5, 5);
        sheet2.addMergedRegion(range);

        //品管
        range = new CellRangeAddress(1, 1, 6, 10);
        sheet2.addMergedRegion(range);
        //二研油粉
        range = new CellRangeAddress(1, 2, 11, 11);
        sheet2.addMergedRegion(range);
        // 设定单元格
        range = new CellRangeAddress(1, 1, 12, 15);
        sheet2.addMergedRegion(range);
//        设定上限
        range = new CellRangeAddress(2, 2, 12, 13);
        sheet2.addMergedRegion(range);
///设定上下
        range = new CellRangeAddress(2, 2, 14, 15);
        sheet2.addMergedRegion(range);
        //一研
        range = new CellRangeAddress(1, 2, 16, 16);
        sheet2.addMergedRegion(range);
        //过粉
        range = new CellRangeAddress(1, 1, 17, 18);
        sheet2.addMergedRegion(range);
//斜线
        range = new CellRangeAddress(1, 1, 19, 20);
        sheet2.addMergedRegion(range);
        //斜线取线
        range = new CellRangeAddress(1, 1, 21, 23);
        sheet2.addMergedRegion(range);
        //斜线取线 过线模
        range = new CellRangeAddress(2, 2, 21, 22);
        sheet2.addMergedRegion(range);
        //一设
        range = new CellRangeAddress(1, 1, 24, 25);
        sheet2.addMergedRegion(range);
        range = new CellRangeAddress(1, 1, 26, 27);
        sheet2.addMergedRegion(range);
        range = new CellRangeAddress(1, 1, 28, 29);
        sheet2.addMergedRegion(range);
        range = new CellRangeAddress(1, 1, 30, 31);
        sheet2.addMergedRegion(range);

        range = new CellRangeAddress(1, 1, 33, 37);
        sheet2.addMergedRegion(range);

        range = new CellRangeAddress(2, 2, 33, 34);
        sheet2.addMergedRegion(range);

        range = new CellRangeAddress(2, 2, 35, 36);
        sheet2.addMergedRegion(range);

        range = new CellRangeAddress(1, 2, 38, 38);
        sheet2.addMergedRegion(range);
        range = new CellRangeAddress(1, 2, 39, 39);
        sheet2.addMergedRegion(range);
        // 一研单元格
//        range = new CellRangeAddress(1, 2, 12, 12);
//        sheet2.addMergedRegion(range);
//
//        range = new CellRangeAddress(1, 1, 13, 14);
//        sheet2.addMergedRegion(range);
//
//        range = new CellRangeAddress(1, 1, 15, 16);
//        sheet2.addMergedRegion(range);
//
//        range = new CellRangeAddress(1, 1, 17, 18);
//        sheet2.addMergedRegion(range);
//
//        range = new CellRangeAddress(1, 1, 19, 21);
//        sheet2.addMergedRegion(range);
//
//        range = new CellRangeAddress(2, 2, 19, 20);
//        sheet2.addMergedRegion(range);
//
//
//        range = new CellRangeAddress(1, 1, 22, 26);
//        sheet2.addMergedRegion(range);
//
//        range = new CellRangeAddress(2, 2, 22, 23);
//        sheet2.addMergedRegion(range);
//
//        range = new CellRangeAddress(2, 2, 24, 25);
//        sheet2.addMergedRegion(range);
//
//
//        range = new CellRangeAddress(1, 2, 27, 27);
//        sheet2.addMergedRegion(range);
//
//        range = new CellRangeAddress(1, 2, 28, 28);
//        sheet2.addMergedRegion(range);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); //左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); //上下居中
        row = sheet2.createRow(1);
        cell = row.createCell(0, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("序号");
        cell = row.createCell(1, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("材质");
        cell = row.createCell(2, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("规格");
        cell = row.createCell(3, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("需到孔径");

        cell = row.createCell(4, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("角度");

        cell = row.createCell(5, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("定径长度");

        cell = row.createCell(6, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("品管");

        cell = row.createCell(11, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("二研");

        cell = row.createCell(12, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("设定");

        cell = row.createCell(16, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("一研");

        cell = row.createCell(17, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("SD+ND)0.30以上过粉");

        cell = row.createCell(19, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("0.30以下斜线");


        cell = row.createCell(21, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("斜线取线");

        cell = row.createCell(24, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("(SD+ND)0.30以上一设");

        cell = row.createCell(26, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("ND0.30以下过粉");

        cell = row.createCell(28, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("成形");

        cell = row.createCell(30, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("扩大");


        cell = row.createCell(32, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("整形");


        cell = row.createCell(33, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("激光参数");

        cell = row.createCell(38, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("µ数");

        cell = row.createCell(39, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("正切值");

        row = sheet2.createRow(2);
        cell = row.createCell(6, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("包装");


        cell = row.createCell(7, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("打码");

        cell = row.createCell(8, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("车制");

        cell = row.createCell(9, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("挪检");
        cell = row.createCell(10, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("镜检");


        cell = row.createCell(12, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("上限");

        cell = row.createCell(15, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("下限");


        cell = row.createCell(17, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("上限");


        cell = row.createCell(18, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("下限");


        cell = row.createCell(19, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("上限");

        cell = row.createCell(20, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("下限");


        cell = row.createCell(21, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("过线模");

        cell = row.createCell(23, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("母线");

        cell = row.createCell(24, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("上限");

        cell = row.createCell(25, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("下限");


        cell = row.createCell(26, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("上限");

        cell = row.createCell(27, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("下限");


        cell = row.createCell(28, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("上限");

        cell = row.createCell(29, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("下限");


        cell = row.createCell(30, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("上限");

        cell = row.createCell(31, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("下限");


        cell = row.createCell(33, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("上限");

        cell = row.createCell(35, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("下限");


        cell = row.createCell(37, CellType.STRING);
        cell.setCellStyle(style);
        cell.setCellValue("角度");

        index = 3;
        for (Parameter parameter : parameters) {
            row = sheet2.createRow(index++);
            row.createCell(0, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getSeque()));
            row.createCell(1, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getMaterial()));
            row.createCell(2, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getSpecs()));
            row.createCell(3, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getAperture()));
            row.createCell(4, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getAngle()));
            row.createCell(5, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getExtent()));
            row.createCell(6, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getQcbz()));
            row.createCell(7, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getQcdm()));
            row.createCell(8, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getQccz()));
            row.createCell(9, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getQclj()));
            row.createCell(10, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getQcjj()));
            row.createCell(11, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getResearch2()));
            row.createCell(12, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getDjAllowanceUp1()));
            row.createCell(13, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getDjAllowanceUp2()));
            row.createCell(14, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getDjAllowanceDown1()));
            row.createCell(15, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getDjAllowanceDown2()));
            row.createCell(16, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getResearch1()));
            row.createCell(17, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getGf1Up()));
            row.createCell(18, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getGf1Down()));
            row.createCell(19, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getXxAllowanceUp()));
            row.createCell(20, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getXxAllowanceDown()));
            row.createCell(21, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getTakeLine1()));
            row.createCell(22, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getTakeLine2()));
            row.createCell(23, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getTakeLine3()));
            row.createCell(24, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getYsUp()));
            row.createCell(25, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getYsDown()));
            row.createCell(26, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getGf2Up()));
            row.createCell(27, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getGf2Down()));
            row.createCell(28, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getCxAllowanceUp()));
            row.createCell(29, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getCxAllowanceDown()));
            row.createCell(30, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getKdAllowanceUp()));
            row.createCell(31, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getKdAllowanceDown()));
            row.createCell(32, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getPlastic()));
            row.createCell(33, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getLaserParamUp1()));
            row.createCell(34, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getLaserParamUp2()));
            row.createCell(35, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getLaserParamDown1()));
            row.createCell(36, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getLaserParamDown2()));
            row.createCell(37, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getLaserParamAngle()));
            row.createCell(38, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getUNumber()));
            row.createCell(39, CellType.STRING).setCellValue(StringUtil.nullHandle(parameter.getTangentValue()));

        }
        ServletOutputStream out = null;

        try {
            response.setContentType("application/x-download");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(insideOrder.getOrderNo() + ".xls", "UTF-8"));
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (wb != null) {
                    wb.close();
                }
            } catch (Exception e) {
                System.out.println(e);
            }
        }
    }


    public static HSSFCellStyle getCellStyle() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setBorderLeft(BorderStyle.MEDIUM);// 左边框
        style.setBorderRight(BorderStyle.MEDIUM);// 右边框
        style.setBorderTop(BorderStyle.MEDIUM);// 上边框
        return style;
    }


    private static void checkParameter(Sheet sheet) {
        int index = 1;
        Row row = sheet.getRow(index);
        Cell cell = row.getCell(0);
        String value = ExcelUtil.getCellValueByCell(cell);
        if (!"序号".equals(value.trim())) {
            throw new BusinessException("参数格式错误！A列表头应该为【序号】");
        }
        cell = row.getCell(1);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"材质".equals(value.trim())) {
            throw new BusinessException("参数格式错误！B列表头应该为【材质】");
        }
        cell = row.getCell(2);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"规格".equals(value)) {
            throw new BusinessException("参数格式错误！C列表头应该为【规格】");
        }
        cell = row.getCell(3);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"需到孔径".equals(value)) {
            throw new BusinessException("参数格式错误！C列表头应该为【需到孔径】");
        }
        cell = row.getCell(4);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"角度".equals(value)) {
            throw new BusinessException("参数格式错误！D列表头应该为【角度】");
        }
        cell = row.getCell(5);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"定径长度".equals(value)) {
            throw new BusinessException("参数格式错误！F列表头应该为【定径长度】");
        }
        cell = row.getCell(6);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"品管".equals(value)) {
            throw new BusinessException("参数格式错误！G-K列表头应该为【品管】");
        }
        cell = row.getCell(11);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"二研油粉".equals(value)) {
            throw new BusinessException("参数格式错误！G-K列表头应该为【二研油粉】");
        }
        cell = row.getCell(12);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"设定".equals(value)) {
            throw new BusinessException("参数格式错误！M-P列表头应该为【设定】");
        }
        cell = row.getCell(16);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"一研".equals(value)) {
            throw new BusinessException("参数格式错误！Q列表头应该为【一研】");
        }
        cell = row.getCell(17);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"(SD+ND)0.30以上过粉".equals(value)) {
            throw new BusinessException("参数格式错误！R-S列表头应该为【(SD+ND)0.30以上过粉】");
        }
        cell = row.getCell(19);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"0.30以下斜线".equals(value)) {
            throw new BusinessException("参数格式错误！T-U列表头应该为【0.30以下斜线】");
        }
        cell = row.getCell(21);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"斜线取线".equals(value)) {
            throw new BusinessException("参数格式错误！V-X列表头应该为【斜线取线】");
        }
        cell = row.getCell(24);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"(SD+ND)0.30以上一设".equals(value)) {
            throw new BusinessException("参数格式错误！Y-Z列表头应该为【(SD+ND)0.30以上一设】");
        }
        cell = row.getCell(26);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"ND0.30以下过粉".equals(value)) {
            throw new BusinessException("参数格式错误！AA-AB列表头应该为【ND0.30以下过粉】");
        }
        cell = row.getCell(28);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"成形".equals(value)) {
            throw new BusinessException("参数格式错误！AC-AD列表头应该为【成形】");
        }
        cell = row.getCell(30);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"扩大".equals(value)) {
            throw new BusinessException("参数格式错误！AE-AF列表头应该为【扩大】");
        }
        cell = row.getCell(32);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"整形".equals(value)) {
            throw new BusinessException("参数格式错误！AG列表头应该为【整形】");
        }
        cell = row.getCell(33);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"激光参数".equals(value)) {
            throw new BusinessException("参数格式错误！AH-AL列表头应该为【激光参数】");
        }
        cell = row.getCell(38);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"µ数".equals(value)) {
            throw new BusinessException("参数格式错误！AM列表头应该为【µ数】");
        }
        cell = row.getCell(39);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"正切值".equals(value)) {
            throw new BusinessException("参数格式错误！AN列表头应该为【正切值】");
        }
    }

    public static List<Parameter> resolvingParameter(File file, String orderNo, String operator) {
        List<Parameter> list = new ArrayList<>();
        Workbook wb = ExcelUtil.readExcel(file);
        if (wb != null) {
            Sheet sheet = wb.getSheetAt(0);
            checkParameter(sheet);
            int index = 3;
            Row row = sheet.getRow(index);
            while (row != null) {
                Parameter parameter = new Parameter();
                Cell cell = row.getCell(1);
                if (cell != null) {
                    String material = ExcelUtil.getCellValueByCell(cell);
                    if (StringUtil.isEmpty(material)) {
                        break;
                    }
                    parameter.setMaterial(material);
                } else {
                    break;
                }
                cell = row.getCell(0);
                String seque = ExcelUtil.getCellValueByCell(cell);

                parameter.setSeque(seque);

                cell = row.getCell(2);
                String specs = ExcelUtil.getCellValueByCell(cell);
                if (StringUtil.isEmpty(specs)) {
                    row = sheet.getRow(++index);
                    continue;
                }
                //获取规格的double类型
                /**
                 * 0.1300以上规格小数点位数保留4位
                 * 0.1300以下规格小数点位数保留5位
                 */
//                double doubleSpecs = Double.valueOf(specs);
//                double doubleTemp = 0.1300d;
                int length = 5;
//                if (doubleSpecs < doubleTemp) {
//                    length = 5;
//                }
                parameter.setSpecs(specs);

                cell = row.getCell(3);
                String aperture = ExcelUtil.getCellValueByCell(cell);
                parameter.setAperture(aperture);

                cell = row.getCell(4);
                String angle = ExcelUtil.getCellValueByCell(cell);
                parameter.setAngle(angle);

                cell = row.getCell(5); //定经长度
                String extent = ExcelUtil.getCellValueByCell(cell);
                parameter.setExtent(extent);


                cell = row.getCell(6);
                String qcbz = ExcelUtil.getCellValueByCell(cell);
                parameter.setQcbz(qcbz);


                cell = row.getCell(7);
                String qcdm = ExcelUtil.getCellValueByCell(cell);
                parameter.setQcdm(qcdm);


                cell = row.getCell(8);
                String qccz = ExcelUtil.getCellValueByCell(cell);
                parameter.setQccz(qccz);


                cell = row.getCell(9);
                String qclj = ExcelUtil.getCellValueByCell(cell);
                parameter.setQclj(qclj);


                cell = row.getCell(10);
                String qcjj = ExcelUtil.getCellValueByCell(cell);
                parameter.setQcjj(qcjj);


                cell = row.getCell(11);
                String research2 = ExcelUtil.getCellValueByCell(cell);
                parameter.setResearch2(research2);


                cell = row.getCell(12);
                String djAllowanceUp1 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setDjAllowanceUp1(djAllowanceUp1);

                cell = row.getCell(13);
                String djAllowanceUp2 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setDjAllowanceUp2(djAllowanceUp2);
                cell = row.getCell(14);
                String djAllowanceDown1 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setDjAllowanceDown1(djAllowanceDown1);
                cell = row.getCell(15);
                String djAllowanceDown2 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setDjAllowanceDown2(djAllowanceDown2);


                cell = row.getCell(16);
                String research1 = ExcelUtil.getCellValueByCell(cell);
                parameter.setResearch1(research1);

                cell = row.getCell(17);
                String gf1Up = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setGf1Up(gf1Up);

                cell = row.getCell(18);
                String gf1Down = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setGf1Down(gf1Down);

                cell = row.getCell(19);
                String xxAllowanceUp = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setXxAllowanceUp(xxAllowanceUp);
                cell = row.getCell(20);
                String xxAllowanceDown = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setXxAllowanceDown(xxAllowanceDown);

                cell = row.getCell(21);
                String takeLine1 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setTakeLine1(takeLine1);
                cell = row.getCell(22);
                String takeLine2 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setTakeLine2(takeLine2);
                cell = row.getCell(23);
                String takeLine3 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setTakeLine3(takeLine3);


                cell = row.getCell(24);
                String ysUp = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setYsUp(ysUp);

                cell = row.getCell(25);
                String ysDown = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setYsDown(ysDown);


                cell = row.getCell(26);
                String gf2Up = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setGf2Up(gf2Up);

                cell = row.getCell(27);
                String gf2Down = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setGf2Down(gf2Down);


                cell = row.getCell(28);
                String cxAllowanceUp = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setCxAllowanceUp(cxAllowanceUp);
                cell = row.getCell(29);
                String cxAllowanceDown = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setCxAllowanceDown(cxAllowanceDown);


                cell = row.getCell(30);
                String kdAllowanceUp = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setKdAllowanceUp(kdAllowanceUp);
                cell = row.getCell(31);
                String kdAllowanceDown = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setKdAllowanceDown(kdAllowanceDown);


                cell = row.getCell(32);
                String plastic = ExcelUtil.getCellValueByCell(cell);
                parameter.setPlastic(plastic);


                cell = row.getCell(33);
                String laserParamUp1 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setLaserParamUp1(laserParamUp1);
                cell = row.getCell(34);
                String laserParamUp2 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setLaserParamUp2(laserParamUp2);
                cell = row.getCell(35);
                String laserParamDown1 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setLaserParamDown1(laserParamDown1);
                cell = row.getCell(36);
                String laserParamDown2 = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setLaserParamDown2(laserParamDown2);

                cell = row.getCell(37);
                String laserParamAngle = ExcelUtil.getCellValueByCell(cell);
                parameter.setLaserParamAngle(laserParamAngle);


                cell = row.getCell(38);
                String uNumber = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setUNumber(uNumber);
                cell = row.getCell(39);
                String tangentValue = StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length);
                parameter.setTangentValue(tangentValue);


                parameter.setOrderNo(orderNo);
                parameter.setOperator(operator);
                list.add(parameter);
                row = sheet.getRow(++index);
            }
        }
        return list;
    }

    public static List<WorkDone> resolvingWorkDone(File file, String operator) {
        List<WorkDone> list = new ArrayList<>();
        Workbook wb = ExcelUtil.readExcel(file);
        if (wb != null) {
            Sheet sheet = wb.getSheetAt(0);
            int index = 3;
            Row row = sheet.getRow(index);
            while (row != null) {
                WorkDone workDone = new WorkDone();
                Cell cell = row.getCell(0);
                if (cell != null) {
                    String orderNo = ExcelUtil.getCellValueByCell(cell);
                    if (StringUtil.isEmpty(orderNo)) {
                        row = sheet.getRow(++index);
                        continue;
                    }
                    workDone.setOrderNo(orderNo);
                } else {
                    row = sheet.getRow(++index);
                    continue;
                }
                cell = row.getCell(1);
                String seque = ExcelUtil.getCellValueByCell(cell);
                workDone.setSeque(seque);

                cell = row.getCell(2);
                String specs = ExcelUtil.getCellValueByCell(cell);
                workDone.setSpecs(specs);

                cell = row.getCell(3);
                String workmanship = ExcelUtil.getCellValueByCell(cell);
                workDone.setWorkmanship(workmanship);

                cell = row.getCell(4);
                String number1 = ExcelUtil.getCellValueByCell(cell);
                if (StringUtil.isEmpty(number1)) {
                    row = sheet.getRow(++index);
                    continue;
                }
                workDone.setNumber1(number1);

                cell = row.getCell(5);
                String amount1 = ExcelUtil.getCellValueByCell(cell);
                if (StringUtil.isEmpty(amount1)) {
                    row = sheet.getRow(++index);
                    continue;
                }
                workDone.setAmount1(amount1);


                cell = row.getCell(6);
                String number2 = ExcelUtil.getCellValueByCell(cell);
                if (StringUtil.isEmpty(number2)) {
                    row = sheet.getRow(++index);
                    continue;
                }
                workDone.setNumber2(number2);

                cell = row.getCell(7);
                String amount2 = ExcelUtil.getCellValueByCell(cell);
                if (StringUtil.isEmpty(amount2)) {
                    row = sheet.getRow(++index);
                    continue;
                }
                workDone.setAmount2(amount2);
                workDone.setOperator(operator);
                workDone.setCreateTime(DateUtil.getFormatedDateTime());
                list.add(workDone);
                row = sheet.getRow(++index);
            }
        }
        return list;
    }


    // 内部订单解析
    public static Map<String, Object> resolvingInsideOrder(File file, String type) {
        Map<String, Object> map = new HashMap<>();
        Workbook wb = ExcelUtil.readExcel(file);
        if (wb != null) {
            //获取第一个sheet
            Sheet sheet = wb.getSheetAt(0);
            //获取最大行数
            InsideOrder insideOrder = new InsideOrder();
            //获取第一行
            Row row = sheet.getRow(2);
            Cell cell = row.getCell(0);
            String custCompany = ExcelUtil.getCellValueByCell(cell).trim();
            System.out.println(custCompany);
            insideOrder.setCustCompany(custCompany);
            row = sheet.getRow(3);
            cell = row.getCell(0);
            String custOrderNo = ExcelUtil.getCellValueByCell(cell);
            custOrderNo = custOrderNo.replaceAll("客户订单号：", "").trim();
            System.out.println(custOrderNo);
            insideOrder.setCustOrderNo(custOrderNo);
            row = sheet.getRow(4);
            cell = row.getCell(0);
            String customer = ExcelUtil.getCellValueByCell(cell);
            customer = customer.replaceAll("客户编码", "").trim();
            System.out.println(customer);
            insideOrder.setCustomer(customer);
            cell = row.getCell(7);
            String material = ExcelUtil.getCellValueByCell(cell);
            material = material.replaceAll("伸线材质：", "").trim();
            insideOrder.setMaterial(material);
            cell = row.getCell(14);
            String orderNo = ExcelUtil.getCellValueByCell(cell);
            orderNo = orderNo.replaceAll("内部订单号：", "").trim();
            System.out.println(orderNo);
            insideOrder.setOrderNo(orderNo);
            row = sheet.getRow(5);
            cell = row.getCell(0);
            String orderDate = ExcelUtil.getCellValueByCell(cell);
            orderDate = orderDate.replaceAll("订单日期：", "").trim();
            System.out.println(orderDate);
            insideOrder.setOrderDate(orderDate);
            cell = row.getCell(7);
            String readyDate = ExcelUtil.getCellValueByCell(cell);
            readyDate = readyDate.replaceAll("物料齐备日期 ：", "").trim();
            System.out.println(readyDate);
            insideOrder.setReadyDate(readyDate);
            cell = row.getCell(14);
            String requireDate = ExcelUtil.getCellValueByCell(cell);
            requireDate = requireDate.replaceAll("要求交期：", "").trim();
            System.out.println(requireDate);
            insideOrder.setRequireDate(requireDate);
            cell = row.getCell(21);
            String replyDate = ExcelUtil.getCellValueByCell(cell);
            replyDate = replyDate.replaceAll("回复交期：", "").trim();
            System.out.println(replyDate);
            insideOrder.setReplyDate(replyDate);
            row = sheet.getRow(6);
            cell = row.getCell(0);
            String requirement = ExcelUtil.getCellValueByCell(cell);
            requirement = requirement.replaceAll("客户要求：", "").trim();
            System.out.println(requirement);
            insideOrder.setRequirement(requirement);

            row = sheet.getRow(7);
            cell = row.getCell(0);
            String requirement2 = ExcelUtil.getCellValueByCell(cell);
            requirement2 = requirement2.replaceAll("内部特殊要求", "").replace("：", "").trim();
            System.out.println(requirement2);
            insideOrder.setRequirement2(requirement2);

            int realLastRowIndex = realLastRowIndex(sheet);
            row = sheet.getRow(realLastRowIndex);
            cell = row.getCell(0);
            String productionDate = ExcelUtil.getCellValueByCell(cell);
            productionDate = productionDate.replace("日期：", "");
            insideOrder.setProductionDate(productionDate);
            cell = row.getCell(5);
            String qcDate = ExcelUtil.getCellValueByCell(cell);
            qcDate = qcDate.replace("日期：", "");
            insideOrder.setQcDate(qcDate);

            cell = row.getCell(10);
            String planDate = ExcelUtil.getCellValueByCell(cell);
            planDate = planDate.replace("日期：", "");
            insideOrder.setPlanDate(planDate);


            cell = row.getCell(15);
            String technologyDate = ExcelUtil.getCellValueByCell(cell);
            technologyDate = technologyDate.replace("日期：", "");
            insideOrder.setTechnologyDate(technologyDate);

            cell = row.getCell(20);
            String businessDate = ExcelUtil.getCellValueByCell(cell);
            businessDate = businessDate.replace("日期：", "");
            insideOrder.setBusinessDate(businessDate);

            row = sheet.getRow(realLastRowIndex - 1);
            cell = row.getCell(0);
            String production = ExcelUtil.getCellValueByCell(cell);
            production = production.replaceAll("生产课：", "").trim();
            System.out.println(production);
            insideOrder.setProduction(production);
            cell = row.getCell(5);
            String qc = ExcelUtil.getCellValueByCell(cell);
            qc = qc.replaceAll("品管：", "").trim();
            System.out.println(qc);
            insideOrder.setQc(qc);

            cell = row.getCell(10);
            String plan = ExcelUtil.getCellValueByCell(cell);
            plan = plan.replaceAll("计划：", "").trim();
            insideOrder.setPlan(plan);

            cell = row.getCell(15);
            String technology = ExcelUtil.getCellValueByCell(cell);
            technology = technology.replaceAll("技术：", "").trim();
            insideOrder.setTechnology(technology);

            cell = row.getCell(20);
            String business = ExcelUtil.getCellValueByCell(cell);
            business = business.replaceAll("业务：", "").trim();
            System.out.println(business);

            insideOrder.setBusiness(business);
            row = sheet.getRow(realLastRowIndex - 2);
            cell = row.getCell(0);
            String remark = ExcelUtil.getCellValueByCell(cell);
            remark = remark.replaceAll("注：", "").trim();
            System.out.println(remark);
            insideOrder.setRemark(remark);
            insideOrder.setStatus(Constant.ORDER_STATUS_0);
            //解析订单项数据
            List<OrderItem> list = orderItem(sheet, orderNo, type);
            insideOrder.setType(type);
            System.out.println(JSON.toJSON(insideOrder));
            map.put("insideOrder", insideOrder);
            map.put("list", list);
        }
        return map;
    }


    private static void checkOrderTitle(Sheet sheet, String orderNo, String type) {
        int index = 9;
        Row row = sheet.getRow(index);
        Cell cell = row.getCell(0);
        String value = "";
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"序号".equals(value.trim())) {
            throw new BusinessException("订单格式错误！A列表头应该为【序号】");
        }
        cell = row.getCell(1);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"存货代码".equals(value.trim())) {
            throw new BusinessException("订单格式错误！B列表头应该为【存货代码】");
        }
        cell = row.getCell(2);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"客户品名".equals(value.trim())) {
            throw new BusinessException("订单格式错误！C列表头应该为【客户品名】");
        }
        cell = row.getCell(3);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"出口模".equals(value.trim())) {
            throw new BusinessException("订单格式错误！D列表头应该为【出口模】");
        }
        cell = row.getCell(4);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"类型备注".equals(value.trim())) {
            throw new BusinessException("订单格式错误！E列表头应该为【类型备注】");
        }
        cell = row.getCell(5);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"规格".equals(value.trim())) {
            throw new BusinessException("订单格式错误！F列表头应该为【规格】");
        }
        cell = row.getCell(6);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"单位".equals(value.trim())) {
            throw new BusinessException("订单格式错误！G列表头应该为【单位】");
        }
        cell = row.getCell(7);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"数量".equals(value.trim())) {
            throw new BusinessException("订单格式错误！H列表头应该为【数量】");
        }
        cell = row.getCell(8);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"公差下限".equals(value.trim())) {
            throw new BusinessException("订单格式错误！I列表头应该为【公差下限】");
        }
        cell = row.getCell(9);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"公差上限".equals(value.trim())) {
            throw new BusinessException("订单格式错误！J列表头应该为【公差上限】");
        }

        cell = row.getCell(10);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"外壳尺寸".equals(value.trim())) {
            throw new BusinessException("订单格式错误！K列表头应该为【外壳尺寸】");
        }
        cell = row.getCell(11);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"客户指定料号".equals(value.trim())) {
            throw new BusinessException("订单格式错误！L列表头应该为【客户指定料号】");
        }
        cell = row.getCell(12);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"压缩角/公差下限".equals(value.trim())) {
            throw new BusinessException("订单格式错误！M列表头应该为【压缩角/公差下限】");
        }
        cell = row.getCell(13);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"压缩角/公差上限".equals(value.trim())) {
            throw new BusinessException("订单格式错误！N列表头应该为【压缩角/公差上限】");
        }
        cell = row.getCell(14);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"定径公差下限".equals(value.trim())) {
            throw new BusinessException("订单格式错误！O列表头应该为【定径公差下限】");
        }
        cell = row.getCell(15);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"定径公差上限".equals(value.trim())) {
            throw new BusinessException("订单格式错误！P列表头应该为【定径公差上限】");
        }
        cell = row.getCell(16);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"供应商代码".equals(value.trim())) {
            throw new BusinessException("订单格式错误！Q列表头应该为【供应商代码】");
        }
        cell = row.getCell(17);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"料号".equals(value.trim())) {
            throw new BusinessException("订单格式错误！R列表头应该为【料号】");
        }
        cell = row.getCell(18);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"压缩角公差下限2".equals(value.trim())) {
            throw new BusinessException("订单格式错误！S列表头应该为【压缩角公差下限2】");
        }
        cell = row.getCell(19);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"压缩角公差上限2".equals(value.trim())) {
            throw new BusinessException("订单格式错误！T列表头应该为【压缩角公差上限2】");
        }
        cell = row.getCell(20);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"定径公差下限2".equals(value.trim())) {
            throw new BusinessException("订单格式错误！U列表头应该为【定径公差下限2】");
        }
        cell = row.getCell(21);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"定径公差上限2".equals(value.trim())) {
            throw new BusinessException("订单格式错误！V列表头应该为【定径公差上限2】");
        }
        cell = row.getCell(22);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"客户代码".equals(value.trim())) {
            throw new BusinessException("订单格式错误！W列表头应该为【客户代码】");
        }
        cell = row.getCell(23);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"模具起始编号".equals(value.trim())) {
            throw new BusinessException("订单格式错误！X列表头应该为【模具起始编号】");
        }
        cell = row.getCell(24);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"模具结束编码".equals(value.trim())) {
            throw new BusinessException("订单格式错误！Y列表头应该为【模具结束编码】");
        }
        cell = row.getCell(25);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"正面9点".equals(value.trim())) {
            throw new BusinessException("订单格式错误！Z列表头应该为【正面9点】");
        }
        cell = row.getCell(26);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"正面3点".equals(value.trim())) {
            throw new BusinessException("订单格式错误！AA列表头应该为【正面3点】");
        }
        cell = row.getCell(27);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"反面9点".equals(value.trim())) {
            throw new BusinessException("订单格式错误！AB列表头应该为【反面9点】");
        }
        cell = row.getCell(28);
        value = ExcelUtil.getCellValueByCell(cell);
        if (!"反面3点".equals(value.trim())) {
            throw new BusinessException("订单格式错误！L列表头应该为【反面3点】");
        }

    }

    private static List<OrderItem> orderItem(Sheet sheet, String orderNo, String type) {

        /**
         * 需求：2022-08-28
         * 0.1300以上规格小数点位数保留4位
         * 0.1300以下规格小数点位数保留5位
         *
         */
        // 校验订单表格式
        checkOrderTitle(sheet, orderNo, type);
        int index = 10;
        Row row = sheet.getRow(index);
        Cell cell = row.getCell(0);
        String seque = ExcelUtil.getCellValueByCell(cell);
        List<OrderItem> list = new ArrayList<>();
        while (!seque.contains("合计")) {
            OrderItem orderItem = new OrderItem();
            orderItem.setOrderNo(orderNo);
            orderItem.setType(type);
            orderItem.setSeque(seque);
            cell = row.getCell(1);
            orderItem.setProductName(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(2);
            orderItem.setCustProcuctName(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(3);
            orderItem.setExport(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(4);
            orderItem.setExcessive(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(5);
            String specs = ExcelUtil.getCellValueByCell(cell);
            //获取规格的double类型
            /**
             * 0.1300以上规格小数点位数保留4位
             * 0.1300以下规格小数点位数保留5位
             */
            int length = 5;
//            double doubleSpecs = Double.valueOf(specs);
//            double doubleTemp = 0.1300d;
//            int length = 4;
//            if (doubleSpecs < doubleTemp) {
//                length = 5;
//            }
            orderItem.setSpecs(specs);
            cell = row.getCell(6);
            orderItem.setUnit(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(7);
            orderItem.setAmount(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(8);
            orderItem.setToleranceUp(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));
            cell = row.getCell(9);
            orderItem.setToleranceDown(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));
            cell = row.getCell(10);
            orderItem.setShellSize(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(11);
            orderItem.setItemNo(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(12);
            orderItem.setCompressDown(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));
            cell = row.getCell(13);
            orderItem.setCompressUp(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));
            cell = row.getCell(14);
            orderItem.setDingjingDown(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));
            cell = row.getCell(15);
            orderItem.setDingjingUp(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));

            cell = row.getCell(16);
            orderItem.setRoughSupplier(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(17);
            orderItem.setRoughItemNo(ExcelUtil.getCellValueByCell(cell));


            cell = row.getCell(18);
            orderItem.setCompressDown2(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));
            cell = row.getCell(19);
            orderItem.setCompressUp2(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));
            cell = row.getCell(20);
            orderItem.setDingjingDown2(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));
            cell = row.getCell(21);
            orderItem.setDingjingUp2(StringUtil.stringPointLength(ExcelUtil.getCellValueByCell(cell), length));


            cell = row.getCell(22);
            orderItem.setCustNo(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(23);
            orderItem.setNumberStart(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(24);
            orderItem.setNumberEnd(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(25);
            orderItem.setFront9(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(26);
            orderItem.setFront3(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(27);
            orderItem.setReverse9(ExcelUtil.getCellValueByCell(cell));
            cell = row.getCell(28);
            orderItem.setReverse3(ExcelUtil.getCellValueByCell(cell));

            if (StringUtil.isNum(seque)) {
                list.add(orderItem);
            }

            try {
                System.out.println(JsonUtil.object2Json(orderItem));
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
            index++;
            row = sheet.getRow(index);
            cell = row.getCell(0);
            // 如果这个序号不是数字 则说明已经读取完毕了
            seque = ExcelUtil.getCellValueByCell(cell);
            if (!StringUtil.isNum(seque)) {
                return list;
            }
        }
        return list;
    }


    private static int realLastRowIndex(Sheet sheet) {
        int rownum = sheet.getLastRowNum();
//        sheet.getLastRowNum()
        System.out.println(sheet.getLastRowNum());
        Row lastRow = sheet.getRow(rownum);
        Cell cell = lastRow.getCell(0);
        String value = ExcelUtil.getCellValueByCell(cell);
        while (!value.contains("日期")) {
            lastRow = sheet.getRow(--rownum);
            System.out.println("rownum==" + rownum);
            if (rownum == -1) {
                throw new BusinessException("订单格式错误，最后一行的A列应该为【日期】");
            }
            if (lastRow == null) {
                continue;
            }
            cell = lastRow.getCell(0);
            value = ExcelUtil.getCellValueByCell(cell);
        }
        return rownum;
    }
}
